package cn.kgc.jdbc.demo;

import cn.kgc.jdbc.entity.Student;
import cn.kgc.jdbc.entity.User;
import cn.kgc.jdbc.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: mosin
 * @version: v1.0  2022/4/21
 * 动态sql的使用
 */
public class TestDynamicSQL {

    public static void main(String[] args) {
        test03();
    }

    /**
     *  查询学生信息   查询的字段
     * stu_name
     * stu_no
     * stu_age
     *  1.没有任何的参数     select *   from student    查询所有
     *  2.参数只有stu_name  select *   from student where stu_name = ?
     *  3.参数有两个 stu_name  stu_age    select *   from student where stu_name = ? and stu_age = ?
     *  4.参数有三个       select *   from student where stu_name = ? and stu_age = ? and stu_no = ?
     */
    public static void test01(){


        //模拟一个前台传来的参数  student
        Student student = Student.builder().stuNo("HFYY1831212").build();
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select  stu_no AS stuNo , stu_name AS stuName ,stu_age AS stuAge   from student where 1=1 ";
        //创建集合 保存参数
        ArrayList params = new ArrayList();
        //判定属性是否为null  拼接sql
        if(student.getStuName()!=null){
            sql+=" and stu_name = ?";
            params.add(student.getStuName());
        }

        if(student.getStuAge()!=null){
            sql+=" and stu_age = ?";
            params.add(student.getStuAge());
        }

        if(student.getStuNo()!=null){
          sql+=" and stu_no = ?";
          params.add(student.getStuNo());
        }

        System.out.println("sql = " + sql);
        System.out.println("params = " + params);
        try {
            List<Student> students = queryRunner.query(sql, new BeanListHandler<>(Student.class), params.toArray());
            System.out.println("总条数:"+students.size());
            students.forEach(stu-> System.out.println("student1 = " + stu));

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    //如何在插入数据后 获取数据库中自增的主键
    public static void test02(){
        try {
            User user = User.builder().uname("kk").pwd("123456").build();
            System.out.println("user1 = " + user);
            Connection connection = DruidUtil.getConnection();
            String sql = "insert into usr (uname,pwd) values(?,?)";
            // Statement.RETURN_GENERATED_KEYS  在插入完成之后 返回自增的主键
            PreparedStatement preparedStatement = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, user.getUname());
            preparedStatement.setString(2, user.getPwd());
            int i = preparedStatement.executeUpdate();
            //获取自增主键
            ResultSet rest = preparedStatement.getGeneratedKeys();
            rest.next();
            Object object = rest.getInt(1);
            System.out.println("object = " + object);

            System.out.println("user2 = " + user);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


    }

    public static void test03(){

        Connection connection = DruidUtil.getConnection();


        String sql1 = "delete from usr where id = ?";
        String sql2 = "delete from usr where id = ?";
        try {
            //设置事务的提交方式
            connection.setAutoCommit(false);
            PreparedStatement preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement1.setInt(1, 17);
            int line = preparedStatement1.executeUpdate();
            System.out.println("line1 = " + line);

            //int  i = 1/0;

            PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement2.setInt(1, 18);
            line = preparedStatement2.executeUpdate();
            System.out.println("line2 = " + line);
            //手动提交事务
            connection.commit();
        } catch (Exception throwables) {
            throwables.printStackTrace();
            //有异常事务回滚
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }


}
